﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Windows.Forms;
using Model_Layer;

namespace Data_Access_Layer
{
    public class DBFlight : IDBFlight
    {
        private SqlConnection con;

        public DBFlight()
        {
            con = Connection.GetInstance().GetConnection();
        }

        #region GetSelectedFlight
        //Retrieve a single flight row based on the given ID: using SingleWhere
        public Flight getSelectedFlight(int idflight, bool retrieveAssociation)
        {
            string wClause = " WHERE idflight = '" + idflight + "'";
            return SingleWhere(wClause, retrieveAssociation);
        }
        #endregion

        #region getAllFlight using MichWhere
        //Retrieve all rows in Flight Table: using MichWhere 
        public List<Flight> GetAllFlights(bool retrieveAssociation)
        {
            try
            {
                return MiscWhere("", retrieveAssociation);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region GetCriteriaFlight
        public List<Flight> GetCriteriaFlights(string wClause, bool retrieveAssociation)
        {
            try
            {
                return MiscWhere(wClause, retrieveAssociation); // Enter our search criteria in first parameter
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        #endregion

        #region MiscWhere
        private List<Flight> MiscWhere(string wClause, bool retrieveAssociation)
        {
            SqlDataReader reader = null;
            List<Flight> flightList = new List<Flight>();
            string query = BuildQuery(wClause);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    //MessageBox.Show("From mishWhere " + reader["timeofdeparture"].ToString() +" "+ reader["idflight"].ToString());
                    Flight objflight = BuildFlight(reader);
                    flightList.Add(objflight);
                }
                con.Close();

                #region RetrieveAssociation
                if (retrieveAssociation)
                {
                    foreach (Flight flightObj in flightList)
                    {
                        // Based on Plane ID, retreive a complete airplane object from Airplane Database
                        IDBAirplane dbAirplane = new DBAirplane();
                        flightObj.Objairplane = dbAirplane.getSpecificAirplane(flightObj.Objairplane.Idairplane);

                        // Based on Airport ID, retreive a complete airport object from Airport Database
                        IDBAirport dbAirport = new DBAirport();
                        flightObj.StartAirport = dbAirport.getSelectedAirport(flightObj.StartAirport.IdAirport);
                        flightObj.DestinationAirport = dbAirport.getSelectedAirport(flightObj.DestinationAirport.IdAirport);
                    }
                    //con.Close();
                }//end if
                #endregion

            }//end try
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return flightList;
        }
        #endregion

        #region SingleWhere
        private Flight SingleWhere(string wClause, bool retrieveAssociation)
        {
            SqlDataReader reader;
            Flight objFlight = null;
            string query = BuildQuery(wClause);
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    objFlight = BuildFlight(reader);
                }
                con.Close();
                if(retrieveAssociation)
                {
                    IDBAirplane dbairplane = new DBAirplane();
                    objFlight.Objairplane = dbairplane.getSpecificAirplane(objFlight.Objairplane.Idairplane);
                    IDBAirport dbAirport = new DBAirport();
                    objFlight.StartAirport = dbAirport.getSelectedAirport(objFlight.StartAirport.IdAirport);
                    objFlight.DestinationAirport = dbAirport.getSelectedAirport(objFlight.DestinationAirport.IdAirport);
                }
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return objFlight;
        }
        #endregion

        #region BuildQuery
        private string BuildQuery(string wClause)
        {
            string value = wClause.ToString();
            string query = "SELECT * FROM Flight";
            if (value.Length > 0)
                query = query + " " + wClause;
            MessageBox.Show(query);
            return query;
        }
        #endregion

        #region BuildFlight
        private Flight BuildFlight(SqlDataReader reader)
        {
            Flight flightObj = null;
            try
            {
                flightObj = new Flight(int.Parse(reader["idflight"].ToString()), new Airplane(int.Parse(reader["idairplane"].ToString())), int.Parse(reader["traveltime"].ToString()), 
                reader["timeofdeparture"].ToString(), reader["timeofarrival"].ToString(),
                int.Parse(reader["availableseats"].ToString()), int.Parse(reader["takenseats"].ToString()), int.Parse(reader["price"].ToString()), 
                new Airport(int.Parse(reader["idstartairport"].ToString())),
                new Airport(int.Parse(reader["iddestinationairport"].ToString())),
                int.Parse(reader["idstartairport"].ToString()), int.Parse(reader["iddestinationairport"].ToString()));
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
           //MessageBox.Show("From buildFlight " + flightObj.Idflight +" " + flightObj.Timeofarrival);
           return flightObj;
        }
        #endregion
    }
}
